package net.yvesd.cellhunter.persistance;

import net.yvesd.cellhunter.model.Cell;
import net.yvesd.cellhunter.model.coverage.SignalStrengthLocation;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class CellHunterDbOpenHelper extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "CELL_HUNTER_DB";

	public static final String GSM_CELL_TABLE_NAME = "GSM_CELL";
	public static final String SIGNAL_STRENGTH_LOCATION_TABLE_NAME = "SIGNAL_STRENGTH_LOCATION";

	private static final String GSM_CELL_TABLE_CREATE = "CREATE TABLE "
			+ GSM_CELL_TABLE_NAME + " (" + "LAC INTEGER, " + "CID INTEGER, "
			+ "NAME TEXT);";

	private static final String SIGNAL_STRENGTH_LOCATION_TABLE_CREATE = "CREATE TABLE "
			+ SIGNAL_STRENGTH_LOCATION_TABLE_NAME
			+ " ("
			+ "ID INTEGER CONSTRAINT PK_SIGNAL_STRENGTH_LOCATION PRIMARY KEY, "
			+ "CELL_LAC INTEGER , "
			+ "CELL_CID INTEGER, "
			+ "LOCATION_LATITUDE REAL, "
			+ "LOCATION_LONGITUDE REAL, "
			+ "LOCATION_ACCURACY REAL, "
			+ "STRENGTH INTEGER, "
			+ "MESUREMENT_DATETIME TEXT); ";

	// +
	// "CONSTRAINT FK_SIGNAL_STRENGTH_LOCATION FOREIGN KEY (CELL_LAC, CELL_ID) REFERENCES "
	// + GSM_CELL_TABLE_NAME + " (LAC, CID);";

	public CellHunterDbOpenHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(GSM_CELL_TABLE_CREATE);
		db.execSQL(SIGNAL_STRENGTH_LOCATION_TABLE_CREATE);
	}

	/**
	 * FIXME gracefully upgrade, not dropping all data like this
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE " + GSM_CELL_TABLE_NAME + ";");
		db.execSQL("DROP TABLE " + SIGNAL_STRENGTH_LOCATION_TABLE_NAME + ";");

		onCreate(db);
	}

	public static long insertCell(Context context, Cell cell) {
		CellHunterDbOpenHelper helper = new CellHunterDbOpenHelper(context);

		SQLiteDatabase database = helper.getWritableDatabase();

		ContentValues contentValues = new ContentValues();
		contentValues.put("LAC", cell.getLac());
		contentValues.put("CID", cell.getCid());
		contentValues.put("NAME", cell.getName());

		Long id = database.insert(GSM_CELL_TABLE_NAME, null, contentValues);

		database.close();

		return id;
	}

	public static void updateCell(Context context, int lac, int cid,
			String newName) {
		CellHunterDbOpenHelper helper = new CellHunterDbOpenHelper(context);

		SQLiteDatabase database = helper.getWritableDatabase();

		String sql = "UPDATE " + GSM_CELL_TABLE_NAME
				+ " SET NAME=? WHERE LAC = ? AND CID = ?";

		database.execSQL(sql, new Object[] { newName, lac, cid });

		database.close();
	}

	public static void deleteSSL(Context context, long id) {

		CellHunterDbOpenHelper helper = new CellHunterDbOpenHelper(context);

		SQLiteDatabase database = helper.getWritableDatabase();

		String sql = "DELETE FROM " + SIGNAL_STRENGTH_LOCATION_TABLE_NAME
				+ " WHERE ID=?";

		database.execSQL(sql, new Object[] { id });

		database.close();
	}

	public static void deleteCell(Context context, int lac, int cid) {

		CellHunterDbOpenHelper helper = new CellHunterDbOpenHelper(context);

		SQLiteDatabase database = helper.getWritableDatabase();

		database.delete(GSM_CELL_TABLE_NAME, "LAC=? AND CID=?", new String[] {
				String.valueOf(lac), String.valueOf(cid) });

		database.close();
	}

	public static void deleteSSLByCell(Context context, int lac, int cid) {
		CellHunterDbOpenHelper helper = new CellHunterDbOpenHelper(context);

		SQLiteDatabase database = helper.getWritableDatabase();

		database.delete(SIGNAL_STRENGTH_LOCATION_TABLE_NAME,
				"CELL_LAC=? AND CELL_CID=?", new String[] { String.valueOf(lac),
						String.valueOf(cid) });

		database.close();
	}

	/**
	 * 
	 * @param context
	 * @param lac
	 * @param cid
	 * @param location
	 * @return The ID of the SignalStrengthLocation
	 */
	public static long insertSSL(Context context, int lac, int cid,
			SignalStrengthLocation location) {

		CellHunterDbOpenHelper helper = new CellHunterDbOpenHelper(context);

		SQLiteDatabase database = helper.getWritableDatabase();

		ContentValues contentValues = new ContentValues();

		contentValues.put("CELL_LAC", lac);
		contentValues.put("CELL_CID", cid);
		contentValues.put("LOCATION_LATITUDE", location.getLocation()
				.getLatitude());
		contentValues.put("LOCATION_LONGITUDE", location.getLocation()
				.getLongitude());
		contentValues.put("LOCATION_ACCURACY", location.getLocation()
				.getAccuracy());
		contentValues.put("STRENGTH", location.getSignalStrengh());
		contentValues.put("MESUREMENT_DATETIME", "UNKNOWN_FIXME"); // FIXME

		long id = database.insert(SIGNAL_STRENGTH_LOCATION_TABLE_NAME, null,
				contentValues);

		database.close();

		return id;
	}
	
	public static String getDbName() {
		return DATABASE_NAME;
	}
}